Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Pass date range in UNION query

Posted on 2009-02-23
2
Medium Priority
?
184 Views
Last Modified: 2012-06-22
I have a script that UNIONs 3 queries which I then export to Excel. Each query uses the same date range (SOP30200.DOCDATE BETWEEN CONVERT(DATETIME, '2009-02-01', 102) AND CONVERT(DATETIME, '2009-02-15', 102)) to display the products sold within that range, I have to do this twice a month, I would like to find a way to enter the date range just once and have it pass it to all the queries.
SELECT     SOP30200.CUSTNAME, SOP30200.CUSTNMBR, SOP30200.FRTAMNT AS Freight_Amt, SOP30200.SHIPMTHD AS SHIPPING_MTHD, 
                      SOP10201.SOPNUMBE AS Invoice_No, SOP30200.DOCDATE AS Invoice_Date, POP30330.ITEMNMBR, POP30330.SERLTNUM, 
                      CASE WHEN IV00101.ITMGEDSC LIKE 'LG%' THEN 'LONG GUN' ELSE ITMGEDSC END AS PRODUCT_TYPE, SOP30300.UNITPRCE AS UNIT_PRICE, 
                      '' AS TAX_PAID, '' AS DATE_PAID, '' AS AMT_PAID, POP30330.DATERECD, POP30300.VENDORID, POP30300.VENDNAME, POP30310.ITEMDESC, 
                      POP30310.PONUMBER AS VENDPONUM
FROM         SOP30200 INNER JOIN
                      SOP10201 ON SOP30200.SOPTYPE = SOP10201.SOPTYPE AND SOP30200.SOPNUMBE = SOP10201.SOPNUMBE INNER JOIN
                      POP30330 INNER JOIN
                      POP30310 ON POP30330.POPRCTNM = POP30310.POPRCTNM AND POP30330.ITEMNMBR = POP30310.ITEMNMBR INNER JOIN
                      POP30300 ON POP30330.POPRCTNM = POP30300.POPRCTNM ON SOP10201.SERLTNUM = POP30330.SERLTNUM AND 
                      SOP10201.ITEMNMBR = POP30330.ITEMNMBR INNER JOIN
                      SOP30300 ON SOP10201.SOPTYPE = SOP30300.SOPTYPE AND SOP10201.SOPNUMBE = SOP30300.SOPNUMBE AND 
                      SOP10201.LNITMSEQ = SOP30300.LNITMSEQ AND SOP10201.CMPNTSEQ = SOP30300.CMPNTSEQ INNER JOIN
                      IV00101 ON SOP10201.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (SOP10201.SOPTYPE = 3) AND (NOT (SOP30200.CUSTNMBR = 'ATI SERVICE')) AND (IV00101.ITMGEDSC IN ('LG558', 'LG035')) AND 
                   (SOP30200.DOCDATE BETWEEN CONVERT(DATETIME, '2009-02-01', 102) AND CONVERT(DATETIME, '2009-02-15', 102))
UNION ALL
SELECT     SOP30200.CUSTNAME, SOP30200.CUSTNMBR, SOP30200.FRTAMNT AS Freight_Amt, SOP30200.SHIPMTHD AS SHIPPING_MTHD, 
                      SOP10201.SOPNUMBE AS Invoice_No, SOP30200.DOCDATE AS Invoice_Date, POP30330.ITEMNMBR, POP30330.SERLTNUM, 
                      CASE WHEN IV00101.ITMGEDSC LIKE 'HG%' THEN 'HAND GUN' ELSE ITMGEDSC END AS PRODUCT_TYPE, SOP30300.UNITPRCE AS UNIT_PRICE, 
                      '' AS TAX_PAID, '' AS DATE_PAID, '' AS AMT_PAID, POP30330.DATERECD, POP30300.VENDORID, POP30300.VENDNAME, POP30310.ITEMDESC, 
                      POP30310.PONUMBER AS VENDPONUM
FROM         SOP30200 INNER JOIN
                      SOP10201 ON SOP30200.SOPTYPE = SOP10201.SOPTYPE AND SOP30200.SOPNUMBE = SOP10201.SOPNUMBE INNER JOIN
                      POP30330 INNER JOIN
                      POP30310 ON POP30330.POPRCTNM = POP30310.POPRCTNM AND POP30330.ITEMNMBR = POP30310.ITEMNMBR INNER JOIN
                      POP30300 ON POP30330.POPRCTNM = POP30300.POPRCTNM ON SOP10201.SERLTNUM = POP30330.SERLTNUM AND 
                      SOP10201.ITEMNMBR = POP30330.ITEMNMBR INNER JOIN
                      SOP30300 ON SOP10201.SOPTYPE = SOP30300.SOPTYPE AND SOP10201.SOPNUMBE = SOP30300.SOPNUMBE AND 
                      SOP10201.LNITMSEQ = SOP30300.LNITMSEQ AND SOP10201.CMPNTSEQ = SOP30300.CMPNTSEQ INNER JOIN
                      IV00101 ON SOP10201.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (SOP10201.SOPTYPE = 3) AND (NOT (SOP30200.CUSTNMBR = 'ATI SERVICE')) AND (IV00101.ITMGEDSC IN ('HG200','HG140')) AND 
                      (SOP30200.DOCDATE BETWEEN CONVERT(DATETIME, '2009-02-01', 102) AND CONVERT(DATETIME, '2009-02-15', 102))
UNION ALL
SELECT     SOP30200.CUSTNAME, SOP30200.CUSTNMBR, SOP30200.FRTAMNT AS Freight_Amt, SOP30200.SHIPMTHD AS SHIPPING_MTHD, 
                      SOP10201.SOPNUMBE AS Invoice_No, SOP30200.DOCDATE AS Invoice_Date, POP30330.ITEMNMBR, POP30330.SERLTNUM, 
                      CASE WHEN IV00101.ITMGEDSC LIKE 'AM%' THEN 'AMMO' ELSE ITMGEDSC END AS PRODUCT_TYPE, SOP30300.UNITPRCE AS UNIT_PRICE, 
                      '' AS TAX_PAID, '' AS DATE_PAID, '' AS AMT_PAID, POP30330.DATERECD, POP30300.VENDORID, POP30300.VENDNAME, POP30310.ITEMDESC, 
                      POP30310.PONUMBER AS VENDPONUM
FROM         SOP30200 INNER JOIN
                      SOP10201 ON SOP30200.SOPTYPE = SOP10201.SOPTYPE AND SOP30200.SOPNUMBE = SOP10201.SOPNUMBE INNER JOIN
                      POP30330 INNER JOIN
                      POP30310 ON POP30330.POPRCTNM = POP30310.POPRCTNM AND POP30330.ITEMNMBR = POP30310.ITEMNMBR INNER JOIN
                      POP30300 ON POP30330.POPRCTNM = POP30300.POPRCTNM ON SOP10201.SERLTNUM = POP30330.SERLTNUM AND 
                      SOP10201.ITEMNMBR = POP30330.ITEMNMBR INNER JOIN
                      SOP30300 ON SOP10201.SOPTYPE = SOP30300.SOPTYPE AND SOP10201.SOPNUMBE = SOP30300.SOPNUMBE AND 
                      SOP10201.LNITMSEQ = SOP30300.LNITMSEQ AND SOP10201.CMPNTSEQ = SOP30300.CMPNTSEQ INNER JOIN
                      IV00101 ON SOP10201.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (SOP10201.SOPTYPE = 3) AND (NOT (SOP30200.CUSTNMBR = 'ATI SERVICE')) AND (IV00101.ITMGEDSC LIKE 'AM52[01]%') AND 
                      (SOP30200.DOCDATE BETWEEN CONVERT(DATETIME, '2009-02-01', 102) AND CONVERT(DATETIME, '2009-02-15', 102))
ORDER BY 9;

Open in new window

0
Comment
Question by:skull52
2 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23714011
Declare @startdate and @enddate variables at the top.....
declare @StartDate datetime
       ,@EndDate   datetime
 
set @StartDate = CONVERT(DATETIME, '2009-02-01', 102) 
set @EndDate = CONVERT(DATETIME, '2009-02-15', 102)
 
SELECT     SOP30200.CUSTNAME, SOP30200.CUSTNMBR, SOP30200.FRTAMNT AS Freight_Amt, SOP30200.SHIPMTHD AS SHIPPING_MTHD, 
                      SOP10201.SOPNUMBE AS Invoice_No, SOP30200.DOCDATE AS Invoice_Date, POP30330.ITEMNMBR, POP30330.SERLTNUM, 
                      CASE WHEN IV00101.ITMGEDSC LIKE 'LG%' THEN 'LONG GUN' ELSE ITMGEDSC END AS PRODUCT_TYPE, SOP30300.UNITPRCE AS UNIT_PRICE, 
                      '' AS TAX_PAID, '' AS DATE_PAID, '' AS AMT_PAID, POP30330.DATERECD, POP30300.VENDORID, POP30300.VENDNAME, POP30310.ITEMDESC, 
                      POP30310.PONUMBER AS VENDPONUM
FROM         SOP30200 INNER JOIN
                      SOP10201 ON SOP30200.SOPTYPE = SOP10201.SOPTYPE AND SOP30200.SOPNUMBE = SOP10201.SOPNUMBE INNER JOIN
                      POP30330 INNER JOIN
                      POP30310 ON POP30330.POPRCTNM = POP30310.POPRCTNM AND POP30330.ITEMNMBR = POP30310.ITEMNMBR INNER JOIN
                      POP30300 ON POP30330.POPRCTNM = POP30300.POPRCTNM ON SOP10201.SERLTNUM = POP30330.SERLTNUM AND 
                      SOP10201.ITEMNMBR = POP30330.ITEMNMBR INNER JOIN
                      SOP30300 ON SOP10201.SOPTYPE = SOP30300.SOPTYPE AND SOP10201.SOPNUMBE = SOP30300.SOPNUMBE AND 
                      SOP10201.LNITMSEQ = SOP30300.LNITMSEQ AND SOP10201.CMPNTSEQ = SOP30300.CMPNTSEQ INNER JOIN
                      IV00101 ON SOP10201.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (SOP10201.SOPTYPE = 3) AND (NOT (SOP30200.CUSTNMBR = 'ATI SERVICE')) AND (IV00101.ITMGEDSC IN ('LG558', 'LG035')) AND 
                   (SOP30200.DOCDATE BETWEEN @StartDate and @EndDate)
UNION ALL
SELECT     SOP30200.CUSTNAME, SOP30200.CUSTNMBR, SOP30200.FRTAMNT AS Freight_Amt, SOP30200.SHIPMTHD AS SHIPPING_MTHD, 
                      SOP10201.SOPNUMBE AS Invoice_No, SOP30200.DOCDATE AS Invoice_Date, POP30330.ITEMNMBR, POP30330.SERLTNUM, 
                      CASE WHEN IV00101.ITMGEDSC LIKE 'HG%' THEN 'HAND GUN' ELSE ITMGEDSC END AS PRODUCT_TYPE, SOP30300.UNITPRCE AS UNIT_PRICE, 
                      '' AS TAX_PAID, '' AS DATE_PAID, '' AS AMT_PAID, POP30330.DATERECD, POP30300.VENDORID, POP30300.VENDNAME, POP30310.ITEMDESC, 
                      POP30310.PONUMBER AS VENDPONUM
FROM         SOP30200 INNER JOIN
                      SOP10201 ON SOP30200.SOPTYPE = SOP10201.SOPTYPE AND SOP30200.SOPNUMBE = SOP10201.SOPNUMBE INNER JOIN
                      POP30330 INNER JOIN
                      POP30310 ON POP30330.POPRCTNM = POP30310.POPRCTNM AND POP30330.ITEMNMBR = POP30310.ITEMNMBR INNER JOIN
                      POP30300 ON POP30330.POPRCTNM = POP30300.POPRCTNM ON SOP10201.SERLTNUM = POP30330.SERLTNUM AND 
                      SOP10201.ITEMNMBR = POP30330.ITEMNMBR INNER JOIN
                      SOP30300 ON SOP10201.SOPTYPE = SOP30300.SOPTYPE AND SOP10201.SOPNUMBE = SOP30300.SOPNUMBE AND 
                      SOP10201.LNITMSEQ = SOP30300.LNITMSEQ AND SOP10201.CMPNTSEQ = SOP30300.CMPNTSEQ INNER JOIN
                      IV00101 ON SOP10201.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (SOP10201.SOPTYPE = 3) AND (NOT (SOP30200.CUSTNMBR = 'ATI SERVICE')) AND (IV00101.ITMGEDSC IN ('HG200','HG140')) AND 
                      (SOP30200.DOCDATE BETWEEN @StartDate and @EndDate)
UNION ALL
SELECT     SOP30200.CUSTNAME, SOP30200.CUSTNMBR, SOP30200.FRTAMNT AS Freight_Amt, SOP30200.SHIPMTHD AS SHIPPING_MTHD, 
                      SOP10201.SOPNUMBE AS Invoice_No, SOP30200.DOCDATE AS Invoice_Date, POP30330.ITEMNMBR, POP30330.SERLTNUM, 
                      CASE WHEN IV00101.ITMGEDSC LIKE 'AM%' THEN 'AMMO' ELSE ITMGEDSC END AS PRODUCT_TYPE, SOP30300.UNITPRCE AS UNIT_PRICE, 
                      '' AS TAX_PAID, '' AS DATE_PAID, '' AS AMT_PAID, POP30330.DATERECD, POP30300.VENDORID, POP30300.VENDNAME, POP30310.ITEMDESC, 
                      POP30310.PONUMBER AS VENDPONUM
FROM         SOP30200 INNER JOIN
                      SOP10201 ON SOP30200.SOPTYPE = SOP10201.SOPTYPE AND SOP30200.SOPNUMBE = SOP10201.SOPNUMBE INNER JOIN
                      POP30330 INNER JOIN
                      POP30310 ON POP30330.POPRCTNM = POP30310.POPRCTNM AND POP30330.ITEMNMBR = POP30310.ITEMNMBR INNER JOIN
                      POP30300 ON POP30330.POPRCTNM = POP30300.POPRCTNM ON SOP10201.SERLTNUM = POP30330.SERLTNUM AND 
                      SOP10201.ITEMNMBR = POP30330.ITEMNMBR INNER JOIN
                      SOP30300 ON SOP10201.SOPTYPE = SOP30300.SOPTYPE AND SOP10201.SOPNUMBE = SOP30300.SOPNUMBE AND 
                      SOP10201.LNITMSEQ = SOP30300.LNITMSEQ AND SOP10201.CMPNTSEQ = SOP30300.CMPNTSEQ INNER JOIN
                      IV00101 ON SOP10201.ITEMNMBR = IV00101.ITEMNMBR
WHERE     (SOP10201.SOPTYPE = 3) AND (NOT (SOP30200.CUSTNMBR = 'ATI SERVICE')) AND (IV00101.ITMGEDSC LIKE 'AM52[01]%') AND 
                      (SOP30200.DOCDATE BETWEEN @StartDate and @EndDate)
ORDER BY 9;

Open in new window

0
 

Author Comment

by:skull52
ID: 23714080
Perfect, Thanks for the quick responce
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question