How to get Below T-SQL qury Output..?

Question

Post reply

How to get Below T-SQL qury Output..?

bhushan_juare

Ten Centuries

Points: 1292
More actions
March 9, 2013 at 1:23 am

#271363

[font="Courier New"]Hi All,
This is the query I have written
DECLARE @FromDate DATETIME
DECLARE @EndDate DATETIME
SET @FromDate = '2013-01-01 00:00:00.000'
SET @EndDate = '2013-02-13 00:00:00.000'
SELECT year(sd.FKDAT) As YEARWISE_DATA,
sg.KUNNR As PARTY,
sg.NAME1 As NAME,
SUM(sd.FKIMG) As QUANTITY,
SUM(sd.NETWR) As VALUE_IN_FC,
SUM(sd.NTGEW) As WEIGHT
FROM
Sales_group sg WITH(NOLOCK)
INNER JOIN
SALES_DATA sd WITH(NOLOCK)
ON
sg.KUNNR = sd.KUNAG
WHERE
sd.FKDAT >= @FromDate
AND
sd.FKDAT <= @EndDate
GROUP By
sd.FKDAT,
sg.KUNNR,
sg.NAME1
ORDER By
1,
sg.KUNNR ASC
Below is the output i am getting,
2013 HA010 ADK 360.000 36988.20 9206.434
2013 HA010 ADK 205.000 31363.80 9299.848
2013 HA018 AGRI 295.000 42646.25 12578.149
2013 HA018 AGRI 119.000 29587.75 8816.112
2013 HA018 AGRI 21.000 10289.65 2882.488
2013 HA018 AGRI 249.000 57764.20 17605.415
Required Output I want
2013 HA010 ADK 565.000 68352.00 18506.31
2013 HA018 AGRI 684.000 140287.85 41882.164
Thanks & Regards,
Bhushan[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Usman Butt Hall of Fame Points: 3186 More actions · Answer 1

I guess you are missing GROUPing BY YEAR clause. Shoud not it be something like this

DECLARE @FromDate DATETIME

DECLARE @EndDate DATETIME

SET @FromDate = '2013-01-01 00:00:00.000'

SET @EndDate = '2013-02-13 00:00:00.000'

SELECT year(sd.FKDAT) As YEARWISE_DATA,

sg.KUNNR As PARTY,

sg.NAME1 As NAME,

SUM(sd.FKIMG) As QUANTITY,

SUM(sd.NETWR) As VALUE_IN_FC,

SUM(sd.NTGEW) As WEIGHT

FROM

Sales_group sg WITH(NOLOCK)

INNER JOIN

SALES_DATA sd WITH(NOLOCK)

ON

sg.KUNNR = sd.KUNAG

WHERE

sd.FKDAT >= @FromDate

AND

sd.FKDAT <= @EndDate

GROUP By

year(sd.FKDAT),

sg.KUNNR,

sg.NAME1

ORDER By

year(sd.FKDAT),

sg.KUNNR ASC

Also using ORDER BY 1 is not the recommended way. Always use column names instead. Hope it helps.

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

Try this. Without test data I wasn't able to test it so it's just an idea off the top of my head.

DECLARE @FromDate DATETIME

DECLARE @EndDate DATETIME

SET @FromDate = '2013-01-01 00:00:00.000'

SET @EndDate = '2013-02-13 00:00:00.000'

SELECT

Result.YEARWISE_DATA,

Result.PARTY,

Result.NAME,

Result.QUANTITY,

Result.VALUE_IN_FC,

Result.WEIGHT

FROM

(

SELECT

year(sd.FKDAT) As YEARWISE_DATA,

sg.KUNNR As PARTY,

sg.NAME1 As NAME,

SUM(sd.FKIMG) OVER (PARTITION BY year(sd.FKDAT), sg.KUNNR, sg.NAME1) As QUANTITY,

SUM(sd.NETWR) OVER (PARTITION BY year(sd.FKDAT), sg.KUNNR, sg.NAME1) As VALUE_IN_FC,

SUM(sd.NTGEW) OVER (PARTITION BY year(sd.FKDAT), sg.KUNNR, sg.NAME1) As WEIGHT

FROM

Sales_group sg WITH (NOLOCK)

INNER JOIN

SALES_DATA sd WITH (NOLOCK)

ON sg.KUNNR = sd.KUNAG

WHERE

sd.FKDAT BETWEEN @FromDate AND @EndDate

) Result

GROUP BY

YEARWISE_DATA,

PARTY,

NAME,

QUANTITY,

VALUE_IN_FC,

WEIGHT

ORDER By

YEARWISE_DATA,

PARTY

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

And be careful of using NOLOCK. Given that this looks like a sales report accuracy might be important and NOLOCK is not going to be accurate.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bhushan_juare Ten Centuries Points: 1292 More actions · Answer 4

[font="Courier New"]Hi Usman,

Thanks for your help.. With some modifications your logic worked..[/font]

bhushan_juare Ten Centuries Points: 1292 More actions · Answer 5

[font="Courier New"]Hi Steven,

From your logic I got to know how PARTITION in SQL worked. Thanks for clearing my concept. I really appreciates your help.. [/font]

ACinAZ SSCommitted Points: 1849 More actions · Answer 6

Something else to point out:

SET @EndDate = '2013-02-13 00:00:00.000'

Midnight (00:00:00.000) is the beginning of the day, not the end. You're essentially getting data from Jan. 1st through Feb. 12th. If you're expecting to get data through the 13th, you won't get it with this format.