Find A, B, C with a condition where A+B+C=30 Chose A, B, C only from given list of number (1,3,5, 7,9,11)

Thanks Sushree for whatapping me this question, but unfortunately the answer is NO we cannot chose  any number from the given list to form A+B+C=30 :D

As a Techie as soon as I got this problem from my cute friend, the only reflection popped up in my mind...Oh!! Put pen to paper write a small block of code and hit upon the answer.

I am so stupid, I didn’t even thought that the given numbers are all ODD in nature, and when you add three odd number you will never going to get even result and here we are expecting to get 30 i.e. even output. :D

But anyways to my best level of stupidity I wrote a small piece of code, which ultimately proves that it is not possible J

DECLARE @no1 INT

       ,@no2 INT

       ,@no3 INT

       ,@no4 INT

CREATE TABLE #temp (

       id INT identity(1, 1)

       ,GivenNo INT

       );

INSERT INTO #temp

SELECT 1 UNION

SELECT 3 UNION

SELECT 5 UNION

SELECT 7 UNION

SELECT 9 UNION

SELECT 11 UNION

SELECT 13 UNION

SELECT 15

WHILE (1 = 1)

BEGIN

       SELECT @no1 = GivenNo

       FROM #temp

       WHERE id = FLOOR(10 * RAND());

       SELECT @no2 = GivenNo

       FROM #temp

       WHERE id = FLOOR(10 * RAND());

       SELECT @no3 = GivenNo

       FROM #temp

       WHERE id = FLOOR(10 * RAND());

       IF isnull(@no1, 0) + isnull(@no2, 0) + isnull(@no3, 0) = 30

       BEGIN

              PRINT cast(@no1 AS VARCHAR(10));

              PRINT cast(@no2 AS VARCHAR(10));

              PRINT cast(@no3 AS VARCHAR(10));

              BREAK

       END

END

DROP TABLE #temp

Expecting a tea with you  :)

Post Reference: Vikram Aristocratic Elfin Share

Leisure time out of most hectic QA release day, playing with Count and Null

It became a leisure time out of most hectic QA release day, when my  friend ask me to design 10 typical query, don’t know why this SQL Server is so interesting it fuels up energy to even a non living atmosphere. Here is my first query goes.

What will be the output of the third query if following first two query produce the below result

select count(*) as TotalCount from dbo.MyTable

TotalCount

-----------

9

select count(*) as WhereCount from dbo.MyTable where MyID = 4

WhereCount

-----------

3

select count(*) as MyCount from dbo.MyTable where MyID <> 4

WhereCount

-----------

??

Before come to any conclusion let’s stimulate the same problem

CREATE TABLE dbo.MyTable

    (

      MyID INT

    , MyChar VARCHAR(10)

    );

   GO

Command(s) completed successfully.

Our table is ready, let’s insert some data and prepare our environment for testing.

INSERT INTO dbo.MyTable

        ( MyID, MyChar )

    VALUES

        ( 1, 'A' ),

        ( 2, 'B' ),

        ( NULL, 'C' ),

        ( 4, 'D' ),

        ( NULL, 'E' ),

        ( 6, 'F' ),

        ( 6, 'G' ),

        ( 4, 'H' ),

        ( 4, 'I' ); 

   GO

Let’s find out the total count of rows in the table

select count(*) from dbo.MyTable

TotalCount

-----------

9

So its 9 in count, lets try to find out now of rows where “MyID” field has value equal to 4

select count(*) as WhereCount from dbo.MyTable where MyID = 4

MyCount

-----------

3

So here it comes out as 3, lets now come to our asked query output

select count(*) as MyCount from dbo.MyTable where MyID <> 4

Here we saw from previous two output that

Total row count = 9

Total row with MyId value equal to 4 is = 3

So if we are asked

Total row with value not equal to 4, in a simple mathematic term we can reply,

Total row with value not equal to 4=(Total row count) – (Total row with MyId value equal to 4)

Total row with value not equal to 4 = 9-3 = 6

Now let’s run the query to find the real answer for the following query

select count(*) as MyCount from dbo.MyTable where MyID <> 4

MyCount

-----------

4

The output is not footing to our thought, lets find out where we went wrong

Lets first query the table data

MyID        MyChar

----------- ----------

1           A

2           B

NULL        C

4           D

NULL        E

6           F

6           G

4           H

4           I

(9 row(s) affected)

From the output we can see total counts of rows are 9 in number and number of rows with MyID equal four are 3. Now if we check number of rows with values not equal to 4:

Considering Null is 6

Not considering Null is 4

Now if we recall our query the count came out as 4

select count(*) as MyCount from dbo.MyTable where MyID <> 4

MyCount

-----------

4

This means MyID <> 4 skip Null values while evaluating count(*) function.

Conclusion: While calculating count, SQL Server skip NULL values.

 

A cup of tea with SQL Server is the greatest combination :)

 

Post Reference: Vikram Aristocratic Elfin Share

A simple but tactical enough, Power of Group By - I

Today my mate draw closer to me for a straight forward script, he had a table which consist of dates in int (yyyymmdd) format, his requisite was to get the latest date of each month.

For example,

Out of 20150123, 20150130, 20150127 the output should be 20150130.

Let’s stimulate the problem by taking an example and lets see how we will approach towards the solution

CREATE table DemoMax

(id int identity(1,1),

mydate int)

insert into DemoMax values (20150226)

insert into DemoMax values (20150131)

insert into DemoMax values (20150329)

insert into DemoMax values (20150429)

insert into DemoMax values (20150530)

insert into DemoMax values (20150626)

insert into DemoMax values (20150726)

insert into DemoMax values (20150826)

insert into DemoMax values(20150226)

insert into DemoMax values(20150227)

insert into DemoMax values(20150328)

Here we have table created with few rows in it.

Our requirement is to get the highest date for each month, for finding so, let’s bifurcate the myDate to yyyy, mm and dd in three separate columns.

SUBSTRING(cast(mydate as varchar),1,4),

SUBSTRING(cast(mydate as varchar),5,2),

Now the second step is to group by the result on the above bifurcated column and applying the max aggregate function to find the largest among all date for a specific yyyy and mm.

max( cast(SUBSTRING(cast(mydate as varchar),7,2) as int) )

Lets formulate the final query

SELECT SUBSTRING(cast(mydate AS VARCHAR), 1, 4) as YYYY

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2) as MM

       ,max(cast(SUBSTRING(cast(mydate AS VARCHAR), 7, 2) AS INT)) as LatestDate

FROM DemoMax

GROUP BY SUBSTRING(cast(mydate AS VARCHAR), 1, 4)

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2)

YYYY MM   LatestDate

---- ---- -----------

2015 01   31

2015 02   27

2015 03   29

2015 04   29

2015 05   30

2015 06   26

2015 07   26

2015 08   26

(8 row(s) affected)

Reformulate the query to get the outout in yyyymmdd format,

with LatestDateCTE

as

(

SELECT SUBSTRING(cast(mydate AS VARCHAR), 1, 4) as YYYY

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2) as MM

       ,max(cast(SUBSTRING(cast(mydate AS VARCHAR), 7, 2) AS INT)) as LatestDate

FROM DemoMax

GROUP BY SUBSTRING(cast(mydate AS VARCHAR), 1, 4)

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2)

)

select (YYYY + MM + LatestDate) as MyDate from LatestDateCTE

MyDate

-----------

201532

201529

201532

201533

201535

201532

201533

201534

(8 row(s) affected)

 

A cup of tea with SQL Server is the greatest combination :)

Post Reference: Vikram Aristocratic Elfin Share