February 24, 2007 at 4:33 am
Hello i have a Query used SQL server 2000 ,its return how many pins sold in 24 hours of any particular day and pins solds from which package. i mean to say in all given default packages i want to check how many pins sold from these in 24 hours of any day may be today dates or user can check this with any previous date.
here is my query ::
select PinPackages.PackageName,
COUNT(CASE WHEN datepart(hh,TransactionDate) = 0 THEN dbo.TransactionLineItems.PinID END) AS [0AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 1 THEN dbo.TransactionLineItems.PinID END) AS [1AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 2 THEN dbo.TransactionLineItems.PinID END) AS [2AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 3 THEN dbo.TransactionLineItems.PinID END) AS [3AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 4 THEN dbo.TransactionLineItems.PinID END) AS [4AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 5 THEN dbo.TransactionLineItems.PinID END) AS [5AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 6 THEN dbo.TransactionLineItems.PinID END) AS [6AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 7 THEN dbo.TransactionLineItems.PinID END) AS [7AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 8 THEN dbo.TransactionLineItems.PinID END) AS [8AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 9 THEN dbo.TransactionLineItems.PinID END) AS [9AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 10 THEN dbo.TransactionLineItems.PinID END) AS [10AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 11 THEN dbo.TransactionLineItems.PinID END) AS [11AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 12 THEN dbo.TransactionLineItems.PinID END) AS [12AM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 13 THEN dbo.TransactionLineItems.PinID END) AS [13PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 14 THEN dbo.TransactionLineItems.PinID END) AS [14PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 15 THEN dbo.TransactionLineItems.PinID END) AS [15PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 16 THEN dbo.TransactionLineItems.PinID END) AS [16PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 17 THEN dbo.TransactionLineItems.PinID END) AS [17PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 18 THEN dbo.TransactionLineItems.PinID END) AS [18PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 19 THEN dbo.TransactionLineItems.PinID END) AS [19PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 20 THEN dbo.TransactionLineItems.PinID END) AS [20PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 21 THEN dbo.TransactionLineItems.PinID END) AS [21PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 22 THEN dbo.TransactionLineItems.PinID END) AS [22PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 23 THEN dbo.TransactionLineItems.PinID END) AS [23PM],
COUNT(CASE WHEN datepart(hh,TransactionDate) = 24 THEN dbo.TransactionLineItems.PinID END) AS [24PM]
FROM dbo.Suppliers INNER JOIN
dbo.Batches ON dbo.Suppliers.SupplierID = dbo.Batches.SupplierID INNER JOIN
dbo.PinPackages ON dbo.Suppliers.SupplierID = dbo.PinPackages.SupplierID INNER JOIN
dbo.Pins ON dbo.Batches.BatchID = dbo.Pins.BatchID AND dbo.PinPackages.PinPackageCode = dbo.Pins.PinPackageCode INNER JOIN
dbo.TransactionLineItems ON dbo.Pins.PinID = dbo.TransactionLineItems.PinID INNER JOIN
dbo.Transactions ON dbo.TransactionLineItems.TransactionID = dbo.Transactions.TransactionID
where TransactionDate >= '12/21/2006' AND TransactionDate < '12/22/2006' AND
Transactions.TransactionTypeID = 3 AND Transactions.TransactionStatusCode = 4
Group By CONVERT(VARCHAR(25),TransactionDate,101),PackageName
this is too much long, is there any other way to do that ?? plz tell me any other simple form for achieving and kindly check this is this right if i want to check this with any package name as which package pins sold in each 24 hours.and chart will always be for today.
i used this for chart.
wait for a reply from all genius plz help me
February 25, 2007 at 6:57 pm
You already have the clue in your SQL:
select PinPackages.PackageName,
datepart(hh,TransactionDate) As [MilitaryTime],
Count(*) As [Count]
FROM dbo.Suppliers INNER JOIN
dbo.Batches ON dbo.Suppliers.SupplierID = dbo.Batches.SupplierID INNER JOIN
dbo.PinPackages ON dbo.Suppliers.SupplierID = dbo.PinPackages.SupplierID INNER JOIN
dbo.Pins ON dbo.Batches.BatchID = dbo.Pins.BatchID AND dbo.PinPackages.PinPackageCode = dbo.Pins.PinPackageCode INNER JOIN
dbo.TransactionLineItems ON dbo.Pins.PinID = dbo.TransactionLineItems.PinID INNER JOIN
dbo.Transactions ON dbo.TransactionLineItems.TransactionID = dbo.Transactions.TransactionID
where TransactionDate >= '12/21/2006' AND TransactionDate < '12/22/2006' AND
Transactions.TransactionTypeID = 3 AND Transactions.TransactionStatusCode = 4
Group By CONVERT(VARCHAR(25),TransactionDate,101),datepart(hh,TransactionDate),PackageName
February 26, 2007 at 9:05 am
Instead of COUNT, you should use SUM in the CASE statement
select PinPackages.PackageName,
SUM(CASE WHEN datepart(hh,TransactionDate) = 0 THEN 1 END) AS [0AM],
SUM(CASE WHEN datepart(hh,TransactionDate) = 1 THEN 1 END) AS [1AM],
SUM(CASE WHEN datepart(hh,TransactionDate) = 2 THEN 1 END) AS [2AM],
SUM(CASE WHEN datepart(hh,TransactionDate) = 3 THEN 1 END) AS [3AM],
SUM(CASE WHEN datepart(hh,TransactionDate) = 4 THEN 1 END) AS [4AM], ....
February 26, 2007 at 12:18 pm
Hi Terry, i didn't get ur point, i want to make count of pins that were sold on that day at each hour can u plz again define the Query in proper format and the other problem is that : when i give range of data in the where clause :
in this it gives me output but on each day it gives me 1 row, but i want in all these days it give me 1 row and give the sum of that pins for example todays hour 3 and tomorrows hour 3 pins sold's count give in the same cell (column) Hour 3pm or 3 am .
i hope u get my point, wait for reply
Thanx in advance..
February 26, 2007 at 2:15 pm
As I do not have your full detail and sample data of the tables, I have to guess.
select PinPackages.PackageName [Package],
CONVERT(VARCHAR(25),TransactionDate,101) As [SaleDate],
datepart(hh,TransactionDate) As [MilitaryTime],
Count(*) As [Count] -- I hoped this returned # of transactions
FROM dbo.Suppliers INNER JOIN
dbo.Batches ON dbo.Suppliers.SupplierID = dbo.Batches.SupplierID INNER JOIN
dbo.PinPackages ON dbo.Suppliers.SupplierID = dbo.PinPackages.SupplierID INNER JOIN
dbo.Pins ON dbo.Batches.BatchID = dbo.Pins.BatchID AND dbo.PinPackages.PinPackageCode = dbo.Pins.PinPackageCode INNER JOIN
dbo.TransactionLineItems ON dbo.Pins.PinID = dbo.TransactionLineItems.PinID INNER JOIN
dbo.Transactions ON dbo.TransactionLineItems.TransactionID = dbo.Transactions.TransactionID
where TransactionDate >= '12/21/2006' AND TransactionDate < '12/25/2006' AND
Transactions.TransactionTypeID = 3 AND Transactions.TransactionStatusCode = 4
Group By CONVERT(VARCHAR(25),TransactionDate,101),datepart(hh,TransactionDate),PackageName
Order By PinPackages.PackageName, [SaleDate], [MilitaryTime]
I am hoping this query returns # of pins sold by date and hour. If this is not what you wanted, please attach your table detail and sample data.
February 26, 2007 at 10:27 pm
Hey Terry once again thanx for ur reply, but i dont need this format of the result, i was doing this at start but as i said u
earlier that query is used for charting, so i need the result in rows for each different package but pins sold result would be shown in
each 24 hoursa of the day, so thats y i m using 23 lines (as u see in the query).
here is my table structure: DATA
TransactionLineItems: TransactionLineItemID 1,2,3,4,5 (in one tran pins pins sold)
TransactionID 1
PinID 1,2,3,4,5
Transactions: TransactionID 1
BuyerID 1
TransactionDate 12/22/2006
TransactionTypeID 1
TransactionStatusCode 2
TotalAmount 150000
PinPackages: PinPackageCode 13100
SupplierID 1
PackageName Mobilink
PackagePrice 100
Pins: PinID 1,2,3,4,5,6,7,8 (in one batch we generate many pins i mean pinIDs)
BatchID 1
PinPackageCode 13100,13100,13100,13100,131000,.....
Batches: BatchID 1
SupplierID 1
BatchIssueDate 12/4/2006
Suppliers: SupplierID 1
CompanyName Mobilink
ContactName XYZ
ContactTitle ABC
Login abc
Password abc123
i hope u got the structure and understand my problem and requirement.
February 27, 2007 at 9:04 am
What are the results you are looking for?
A sum will give you a count, such as
select sum( tl.pinid)
, datepart( hh, t.transactiondate) 'hour'
from transactions t
inner join transactionlineitems tl
on t.transactionid = tl.transactionid
group by datepart(dd, t.transactiondate)
The datepart will divide up the day based on hours and the GROUP BY separates them out. Get the sums by hour separated and then add in the other columns you need from other tables.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 27, 2007 at 5:53 pm
Now I understand what your requirements are. You want to display hourly sales history. Even if there were no sales made you still need to display in a chart or something for report purpose. I think what you have in the original SELECT statement is good enough for that purpose. I also tried to suggest different ways of doing it but it's not better than what you already have. But just for your interest, I will share it with you. It uses a temp table and a cursor.
Set Nocount On
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Batches]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Batches]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PinPackages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PinPackages]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pins]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Pins]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQLCommand]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SQLCommand]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Suppliers]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempReport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TempReport]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TransactionLineItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TransactionLineItems]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Transactions]
Create Table dbo.TransactionLineItems (
TransactionLineItemID INT NOT NULL,
TransactionID INT NOT NULL,
PinID INT NOT NULL)
Insert Into dbo.TransactionLineItems
Select 1,1,1 UNION ALL
Select 2,1,2 UNION ALL
Select 3,1,3 UNION ALL
Select 4,1,4 UNION ALL
Select 5,1,5 UNION ALL
Select 6,1,6 UNION ALL
Select 7,1,7 UNION ALL
Select 8,1,8 UNION ALL
Select 9,2,9 UNION ALL
Select 10,2,10 UNION ALL
Select 11,2,11 UNION ALL
Select 12,2,12 UNION ALL
Select 13,2,13 UNION ALL
Select 14,2,14 UNION ALL
Select 15,2,15 UNION ALL
Select 16,2,16
Create Table dbo.Transactions (
TransactionID INT Not Null,
BuyerID INt Null,
TransactionDate Datetime null,
TransactionTypeID Int Not Null,
TransactionStatusCode Int Null,
TotalAmount Money Null
)
Insert Into dbo.Transactions
Select 1,1,'2006-12-21 21:11:54', 3,4,150000 Union All
Select 2,2,'2006-12-23 11:11:54', 3,4,70000
Create Table dbo.PinPackages (
PinPackageCode INT Not Null,
SupplierID INT Not Null,
PackageName Varchar(20) Not Null,
PackagePrice Money Null)
Insert Into dbo.PinPackages
Select 13100,1, 'Mobilink',100 Union All
Select 13200,2, 'DotNet',80
Create table dbo.Pins (
PinID INT Not Null,
BatchID INT Not Null,
PinPackageCode INT Null)
Insert into dbo.Pins
Select 1,1,13100 Union All
Select 2,1,13100 Union All
Select 3,1,13100 Union All
Select 4,1,13100 Union All
Select 5,1,13100 Union All
Select 6,1,13100 Union All
Select 7,1,13100 Union All
Select 8,1,13100 Union All
Select 9,2,13200 Union All
Select 10,2,13200 Union All
Select 11,2,13200 Union All
Select 12,2,13200 Union All
Select 13,2,13200 Union All
Select 14,2,13200 Union All
Select 15,2,13200 Union All
Select 16,2,13200
Create Table dbo.Batches (
BatchID Int Not Null,
SupplierID INT Not Null,
BatchIssueDate DateTime Null)
Insert Into dbo.Batches
Select 1,1,'2006-12-4' Union All
Select 2,2,'2006-12-24'
Create Table dbo.Suppliers (
SupplierID INT Not Null,
CompanyName Varchar(20) Not Null,
ContactName Varchar(20) Null,
ContactTitle Varchar(20) Null,
Login Varchar(20) Null,
[Password] Varchar(20) Null)
Insert Into dbo.Suppliers
Select 1, 'Mobilink','XYZ', 'ABC', 'abc', 'abc123' Union All
Select 2, 'DotNet','Pub', 'XXX', 'xxx', 'xxx123'
-- Create temp report table for 24 hour display
Create Table dbo.TempReport (
PackageName Varchar(20) Not Null,
BusinessDate DateTime Not Null,
Hour0 INT Default 0,
Hour1 INT Default 0,
Hour2 INT Default 0,
Hour3 INT Default 0,
Hour4 INT Default 0,
Hour5 INT Default 0,
Hour6 INT Default 0,
Hour7 INT Default 0,
Hour8 INT Default 0,
Hour9 INT Default 0,
Hour10 INT Default 0,
Hour11 INT Default 0,
Hour12 INT Default 0,
Hour13 INT Default 0,
Hour14 INT Default 0,
Hour15 INT Default 0,
Hour16 INT Default 0,
Hour17 INT Default 0,
Hour18 INT Default 0,
Hour19 INT Default 0,
Hour20 INT Default 0,
Hour21 INT Default 0,
Hour22 INT Default 0,
Hour23 INT Default 0)
Declare @StartDate DateTime
Declare @EndDate DateTime
Declare @Command as varchar(2000)
Set @StartDate='2006-12-21'
Set @EndDate='2006-12-26'
select
'
Insert Into dbo.TempReport
(PackageName, BusinessDate, Hour' + Cast(datepart(hh,TransactionDate) As Varchar(2)) + ')
Values (''' + IsNull(PinPackages.PackageName, 'Null') + ''','''+ Cast(TransactionDate As Varchar(11)) + ''',' + Cast(Count(*) As Varchar(10)) + ')
' as [Command]
into SQLCommand
FROM dbo.Suppliers INNER JOIN
dbo.Batches ON dbo.Suppliers.SupplierID = dbo.Batches.SupplierID INNER JOIN
dbo.PinPackages ON dbo.Suppliers.SupplierID = dbo.PinPackages.SupplierID INNER JOIN
dbo.Pins ON dbo.Batches.BatchID = dbo.Pins.BatchID AND dbo.PinPackages.PinPackageCode = dbo.Pins.PinPackageCode INNER JOIN
dbo.TransactionLineItems ON dbo.Pins.PinID = dbo.TransactionLineItems.PinID INNER JOIN
dbo.Transactions ON dbo.TransactionLineItems.TransactionID = dbo.Transactions.TransactionID
where TransactionDate >= @StartDate AND TransactionDate < @EndDate AND
Transactions.TransactionTypeID = 3 AND Transactions.TransactionStatusCode = 4
Group By TransactionDate,datepart(hh,TransactionDate),PackageName
DECLARE Report_Cursor CURSOR FOR
SELECT Command
FROM SQLCommand
OPEN Report_Cursor
FETCH NEXT FROM Report_Cursor INTO @Command
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @Command
Exec (@Command)
FETCH NEXT FROM Report_Cursor INTO @Command
END
CLOSE Report_Cursor
DEALLOCATE Report_Cursor
Select * From dbo.TempReport
February 28, 2007 at 3:21 am
hey terry thanx for ur reply, and the solution to do this with the other way, thanx once again, but the problem is ,
when i give a range of dates, it shows the result in different rows as if u run the query u provide, it gives the result on many rows , i need that it shows the recors for each package at one row, and adds the pinssold for each hour of range dates, hope u get my point , i need the result in that format::
PackageName 0hour 1hour 2hour 3hour 4hour 5hour 6hour 7hour 8hour................
Mobilink 0 14 0 7 0 6 0 5 4............. Warid 34 8 0 0 0 0 22 1 4...............
for example in date 21 at hour0 5 pins slod for package waird, and 22 date, at hour 0 20, and at 23 date package warid 9 pins sole so, we take sum of these, all pins sold for between date range of a particular package in one row, not in each row, now tell me is there any solution of that problem?
February 28, 2007 at 7:41 am
The text you give for the example does not show in the sample table. Can you post exactly what you are looking for with multiple dates and sales? At hour 0, there is no
sale of 5 pins.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 28, 2007 at 3:34 pm
Thanks Administrator for your comments. But I understand fully what his new requirements are now. I updated my test data for his new requirement as in the script below.
I added new codes for your requirement. Basic ideas is the same as before to add new cross-tab column, datesold.
Set Nocount On
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Batches]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Batches]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PinPackages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PinPackages]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pins]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Pins]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQLCommand]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SQLCommand]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Suppliers]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempReport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TempReport]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempReport2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TempReport2]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TransactionLineItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TransactionLineItems]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Transactions]
Create Table dbo.TransactionLineItems (
TransactionLineItemID INT NOT NULL,
TransactionID INT NOT NULL,
PinID INT NOT NULL)
Truncate Table dbo.TransactionLineItems
Insert Into dbo.TransactionLineItems
Select 1,1,1 UNION ALL
Select 2,1,2 UNION ALL
Select 3,1,3 UNION ALL
Select 4,1,4 UNION ALL
Select 5,3,5 UNION ALL
Select 6,3,6 UNION ALL
Select 7,3,7 UNION ALL
Select 8,3,8 UNION ALL
Select 9,2,9 UNION ALL
Select 10,2,10 UNION ALL
Select 11,2,11 UNION ALL
Select 12,2,12 UNION ALL
Select 13,4,13 UNION ALL
Select 14,4,14 UNION ALL
Select 15,4,15 UNION ALL
Select 16,4,16
Create Table dbo.Transactions (
TransactionID INT Not Null,
BuyerID INt Null,
TransactionDate Datetime null,
TransactionTypeID Int Not Null,
TransactionStatusCode Int Null,
TotalAmount Money Null
)
Insert Into dbo.Transactions
Select 1,1,'2006-12-21 21:11:54', 3,4,150000 Union All
Select 2,2,'2006-12-23 11:11:54', 3,4,70000 Union All
Select 3,3,'2006-12-22 18:11:54', 3,4,150000 Union All
Select 4,4,'2006-12-24 13:11:54', 3,4,70000
Create Table dbo.PinPackages (
PinPackageCode INT Not Null,
SupplierID INT Not Null,
PackageName Varchar(20) Not Null,
PackagePrice Money Null)
Insert Into dbo.PinPackages
Select 13100,1, 'Mobilink',100 Union All
Select 13200,2, 'DotNet',80
Create table dbo.Pins (
PinID INT Not Null,
BatchID INT Not Null,
PinPackageCode INT Null)
Insert into dbo.Pins
Select 1,1,13100 Union All
Select 2,1,13100 Union All
Select 3,1,13100 Union All
Select 4,1,13100 Union All
Select 5,1,13100 Union All
Select 6,1,13100 Union All
Select 7,1,13100 Union All
Select 8,1,13100 Union All
Select 9,2,13200 Union All
Select 10,2,13200 Union All
Select 11,2,13200 Union All
Select 12,2,13200 Union All
Select 13,2,13200 Union All
Select 14,2,13200 Union All
Select 15,2,13200 Union All
Select 16,2,13200
Create Table dbo.Batches (
BatchID Int Not Null,
SupplierID INT Not Null,
BatchIssueDate DateTime Null)
Insert Into dbo.Batches
Select 1,1,'2006-12-4' Union All
Select 2,2,'2006-12-24'
Create Table dbo.Suppliers (
SupplierID INT Not Null,
CompanyName Varchar(20) Not Null,
ContactName Varchar(20) Null,
ContactTitle Varchar(20) Null,
Login Varchar(20) Null,
[Password] Varchar(20) Null)
Insert Into dbo.Suppliers
Select 1, 'Mobilink','XYZ', 'ABC', 'abc', 'abc123' Union All
Select 2, 'DotNet','Pub', 'XXX', 'xxx', 'xxx123'
-- Create temp report table for 24 hour display
Create Table dbo.TempReport (
PackageName Varchar(20) Not Null,
BusinessDate DateTime Not Null,
Hour0 INT Default 0,
Hour1 INT Default 0,
Hour2 INT Default 0,
Hour3 INT Default 0,
Hour4 INT Default 0,
Hour5 INT Default 0,
Hour6 INT Default 0,
Hour7 INT Default 0,
Hour8 INT Default 0,
Hour9 INT Default 0,
Hour10 INT Default 0,
Hour11 INT Default 0,
Hour12 INT Default 0,
Hour13 INT Default 0,
Hour14 INT Default 0,
Hour15 INT Default 0,
Hour16 INT Default 0,
Hour17 INT Default 0,
Hour18 INT Default 0,
Hour19 INT Default 0,
Hour20 INT Default 0,
Hour21 INT Default 0,
Hour22 INT Default 0,
Hour23 INT Default 0)
Declare @StartDate DateTime
Declare @EndDate DateTime
Declare @Command as varchar(2000)
Set @StartDate='2006-12-21'
Set @EndDate='2006-12-26'
select
'
Insert Into dbo.TempReport
(PackageName, BusinessDate, Hour' + Cast(datepart(hh,TransactionDate) As Varchar(2)) + ')
Values (''' + IsNull(PinPackages.PackageName, 'Null') + ''','''+ Cast(TransactionDate As Varchar(11)) + ''',' + Cast(Count(*) As Varchar(10)) + ')
' as [Command]
into SQLCommand
FROM dbo.Suppliers INNER JOIN
dbo.Batches ON dbo.Suppliers.SupplierID = dbo.Batches.SupplierID INNER JOIN
dbo.PinPackages ON dbo.Suppliers.SupplierID = dbo.PinPackages.SupplierID INNER JOIN
dbo.Pins ON dbo.Batches.BatchID = dbo.Pins.BatchID AND dbo.PinPackages.PinPackageCode = dbo.Pins.PinPackageCode INNER JOIN
dbo.TransactionLineItems ON dbo.Pins.PinID = dbo.TransactionLineItems.PinID INNER JOIN
dbo.Transactions ON dbo.TransactionLineItems.TransactionID = dbo.Transactions.TransactionID
where TransactionDate >= @StartDate AND TransactionDate < @EndDate AND
Transactions.TransactionTypeID = 3 AND Transactions.TransactionStatusCode = 4
Group By TransactionDate,datepart(hh,TransactionDate),PackageName
DECLARE Report_Cursor CURSOR FOR
SELECT Command
FROM SQLCommand
OPEN Report_Cursor
FETCH NEXT FROM Report_Cursor INTO @Command
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @Command
Exec (@Command)
FETCH NEXT FROM Report_Cursor INTO @Command
END
CLOSE Report_Cursor
DEALLOCATE Report_Cursor
Select * From dbo.TempReport
DECLARE @BusinessDate Varchar(50)
DECLARE @SQL Varchar(5000)
DECLARE @SQL2 Varchar(5000)
DECLARE @Num INT
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT [BusinessDate] FROM [dbo].TempReport
GROUP BY [BusinessDate]
ORDER BY [BusinessDate] -- this is important order
SET @SQL = ''
SET @SQL2 = ''
SET @Num = 0
OPEN Fields
FETCH NEXT FROM Fields INTO @BusinessDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Num = @Num + 1
IF @Num = 1
SET @SQL = 'CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),BusinessDate),''NA'') = ''' + @BusinessDate + ''' THEN [BusinessDate] ELSE NULL END)=0 THEN '''' ELSE ''!' + @BusinessDate + ''' END
'
ELSE
SET @SQL = @SQL + '+ CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),BusinessDate),''NA'') = ''' + @BusinessDate + ''' THEN [BusinessDate] ELSE NULL END)=0 THEN '''' ELSE ''!' + @BusinessDate + ''' END
'
FETCH NEXT FROM Fields INTO @BusinessDate
END
CLOSE Fields DEALLOCATE Fields
-- New codes
Declare @SQL3 varchar(2000)
Set @SQL3 = 'SELECT [PackageName],' + @SQL + ' [DateSold], 0,
Sum(Hour0) Hour0, Sum(Hour1) Hour1, Sum(Hour2) Hour2, Sum(Hour3) Hour3,
Sum(Hour4) Hour4, Sum(Hour5) Hour5, Sum(Hour6) Hour6, Sum(Hour7) Hour7,
Sum(Hour8) Hour8, Sum(Hour9) Hour9, Sum(Hour10) Hour10, Sum(Hour11) Hour11,
Sum(Hour12) Hour12, Sum(Hour13) Hour13, Sum(Hour14) Hour14, Sum(Hour15) Hour15,
Sum(Hour16) Hour16, Sum(Hour17) Hour17, Sum(Hour18) Hour18, Sum(Hour19) Hour19,
Sum(Hour20) Hour20, Sum(Hour21) Hour21, Sum(Hour22) Hour22, Sum(Hour23) Hour23
FROM [dbo].TempReport
GROUP BY [PackageName]
ORDER BY [PackageName]
'
--Print @SQL3
Create Table dbo.TempReport2 (
PackageName Varchar(20) Not Null,
DateSold Varchar(200) NULL,
Flag Bit Default 0,
Hour0 INT Default 0,
Hour1 INT Default 0,
Hour2 INT Default 0,
Hour3 INT Default 0,
Hour4 INT Default 0,
Hour5 INT Default 0,
Hour6 INT Default 0,
Hour7 INT Default 0,
Hour8 INT Default 0,
Hour9 INT Default 0,
Hour10 INT Default 0,
Hour11 INT Default 0,
Hour12 INT Default 0,
Hour13 INT Default 0,
Hour14 INT Default 0,
Hour15 INT Default 0,
Hour16 INT Default 0,
Hour17 INT Default 0,
Hour18 INT Default 0,
Hour19 INT Default 0,
Hour20 INT Default 0,
Hour21 INT Default 0,
Hour22 INT Default 0,
Hour23 INT Default 0)
--
-- This is required to compose starting date and end date
Insert Into TempReport2
EXEC (@SQL3)
-- Detect if more than 1 date entered for datesold
-- and set flag to 1
Update TempReport2
Set Flag = 1
From TempReport2
Where CharIndex('!',DateSold,2) <> 0
Select PackageName,
-- Compose dates between first date and last date
Case When Flag = 0 Then Cast(Replace(DateSold, '!','') as Varchar(50))
Else Cast(Substring(DateSold, 2, CharIndex('!',DateSold,2)-2)
+ ' - '
+ Reverse(Substring(Reverse(DateSold), 1, CharIndex('!',Reverse(DateSold),1)-1)) As Varchar(50))
END [DateSold],
Hour0, Hour1, Hour2, Hour3,
Hour4, Hour5, Hour6, Hour7,
Hour8, Hour9, Hour10,Hour11,
Hour12,Hour13,Hour14,Hour15,
Hour16,Hour17,Hour18,Hour19,
Hour20,Hour21,Hour22,Hour23
From TempReport2
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply