July 31, 2019 at 12:22 am
Hello,
trying to think what is the best way to tackle this... i have 1 table, and the requirements is asking me to show a count of certain where clause, example...
i have customer table listed below like:
create table #Temp
(
customerID int,
BoughtBananas int,
boughtApples int,
BoughtGrapes int,
BoughtDate date
)
insert into #Temp
values(1,0,0,1,'2019-01-01')
insert into #Temp
values(2,1,0,1,'2019-01-25')
insert into #Temp
values(3,0,1,1,'2019-01-23')
insert into #Temp
values(4,0,0,1,'2019-01-16')
insert into #Temp
values(5,1,0,0,'2019-01-08')
insert into #Temp
values(6,0,1,0,'2019-01-01')
insert into #Temp
values(7,0,0,1,'2019-01-12')
and i want the output something like:
Apples Bananes Grapes
01-01-2019 1 3 1
01-02-2019 0 2 0
01-03-2019 1 0 0
01-04-2019
01-05-2019
01-06-2019
so i would like to total it up per column, by date, but not sure how to best way to tackle this, any thoughts? examples? sorry i know what i supplied you is not the exact count but its an example 🙁
July 31, 2019 at 1:35 am
unpivot/normalize, filter, repivot?
July 31, 2019 at 6:28 am
This is a simple SUM with GROUP BY
SELECT t.BoughtDate
, Apples = SUM( t.BoughtApples )
, Bananas = SUM( t.BoughtBananas )
, Grapes = SUM( t.BoughtGrapes )
FROM #Temp AS t
GROUP BY t.BoughtDate
ORDER BY t.BoughtDate;
August 1, 2019 at 5:04 pm
Building on DesNorton's statement, you may want to show all the dates in the range, even if those dates have no sales. To do that, you need to build a list of all possible dates, then LEFT JOIN in your actual sales. This solution builds a temporary Tally table, but there are better, more consistent ways to do that. This version is provided as an example, but a better version of the Tally table can be substituted. See Jeff Moden's post for a Tally table function.
WITHTempTally
AS(
SELECTROW_NUMBER() OVER (ORDER BY a.object_id) AS N
FROMsys.columns a, sys.columns b
),
DateRange
AS(
SELECTMIN(BoughtDate) AS StartDate,
MAX(BoughtDate) AS EndDate
FROM#Temp
)
SELECT--N,
BoughtDate= DATEADD(DAY, N - 1, r.StartDate),
Apples= ISNULL(b.Apples, 0),
Bananas= ISNULL(b.Bananas, 0),
Grapes= ISNULL(b.Grapes, 0)
FROMTempTally t
CROSS JOIN DateRange r
LEFT JOIN (
SELECT t.BoughtDate
, Apples = SUM( t.BoughtApples )
, Bananas = SUM( t.BoughtBananas )
, Grapes = SUM( t.BoughtGrapes )
FROM #Temp AS t
GROUP BY t.BoughtDate
) b
ONb.BoughtDate = DATEADD(DAY, N - 1, r.StartDate)
WHEREN <= DATEDIFF(DAY, r.StartDate, r.EndDate) + 1
ORDER BY t.N;
August 1, 2019 at 5:09 pm
thank you all, with the info you gave me, i was able to use the queries and get it right 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply